﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Data;

namespace IHISCETeim.Library
{
    public  class NPOIHelper
    {
        /// <summary>
        ///  根据EXECL路径 获取第一个工作表的数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataTable ImportExcel(string filePath)
        {
            return ImportExcel(filePath, 0);
        }
        /// <summary>
        /// 根据EXECL路径 获取指定工作表的数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetIndex">工作表 索引，从0开始</param>
        /// <returns></returns>
        public static DataTable ImportExcel(string filePath,int sheetIndex)
        {
            HSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }


            HSSFSheet sheet = hssfworkbook.GetSheetAt(sheetIndex) as HSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
           
            int titleRowNum = sheet.FirstRowNum;
            HSSFRow titlerow = (HSSFRow)sheet.GetRow(titleRowNum);
            for (int j = 0; j < titlerow.LastCellNum; j++)
            {
                dt.Columns.Add(titlerow.Cells[j].ToString());
            }
            rows.MoveNext();
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    HSSFCell cell = row.GetCell(i) as HSSFCell;


                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }


    }
}
